from flask import request,jsonify,Blueprint
from db import db1
from comm import get_data

bp_course=Blueprint('course',__name__)
@bp_course.route('/zhiw',methods=['GET','POST','DELETE','PUT'])
def zhiw():
    if request.method=='POST':
        data=get_data()
        sql="insert into zhiwugl(name,bianm,paix) values('%s','%s',%d)"%(data['name'],data['bianm'],int(data['paix']))
        res=db1.insert(sql)
        return jsonify({'mes':"添加成功"})
    
    elif request.method=='GET':
        id=request.args.get('id')
        name=request.args.get('name')
        bianm=request.args.get('bianm')
        if id:
            sql="select * from zhiwugl where id =%d"%(int(id))
            res=db1.find_one(sql)
            return jsonify({'list':res})
        else:
            if name and not bianm:
                print(name)
                sql=f"select * from zhiwugl where name like '%{name}%'"
                res=db1.find(sql)
                return jsonify({'list':res})
            elif bianm and not name:      
                sql=f"select * from zhiwugl where bianm like '%{bianm}%'"
                res=db1.find(sql)
                return jsonify({'list':res})
            if name and bianm:
                sql="select * from zhiwugl where name='%s' || bianm='%s'"%(name,bianm)
                res=db1.find(sql)
                return jsonify({'list':res})
            else:
                sql="select * from zhiwugl"
                res=db1.find(sql)
                return jsonify({'list':res})

    elif request.method=='DELETE':
        id=request.args.get('id')
        sql="delete from zhiwugl where id=%d"%int(id)
        res=db1.insert(sql) 
        return jsonify({'mes':"删除成功"})
    else:
        data=get_data()
        print(data)
        sql="update zhiwugl set name='%s',bianm='%s',paix=%d where id=%d"%(data['name'],data['bianm'],int(data['paix']),int(data['id']))
        res=db1.insert(sql)
        return jsonify({'mes':"修改成功"})





from excels import excel
@bp_course.route('/zwdao')
def dao():
    sql="select name,bianm,paix from zhiwugl"
    lst=['职务名称','职务编码','职务排序']
    filename='filex.xls'
    excel(sql,lst,filename)
    return jsonify({'url':'http://localhost:5000/static/download/'+filename})
from excels import excelr


                                              
@bp_course.route('/zwdaor',methods=['POST'])       
def daor():
    file=request.files.get('file')
    file.save('D:\\p9\pro\\workflow\\static\\upload\\'+file.filename)
    filename1='D:\\p9\pro\\workflow\\static\\upload\\'+str(file.filename)
    sql1="insert into zhiwugl(name,bianm,paix) values"
    num1=4
    excelr(sql1,filename1,num1)
    return jsonify({'mes':'成功'})





# @bp_course.route('/create_index',methods=['GET','POST','DELETE','PUT'])
# def get_kwords():
#     klist=[]
#     for i in catedict.values():
#         list=i.split()
#         klist.extend(list)
#     return set(klist)
# kwords=get_kwords()
# def create_index():
#     dict={}
#     for i in kwords:
#         keylist=[]
#         for j in catedict.keys():
#             if i in catedict[j].split():
#                 keylist.append(j)
#         dict[i]=keylist
#     return dict




